from DataAccess.DBConnFactory import DBConnFactory

from string import Template

def query_onshore_hld(ref_date):
	
	sql_tpl = Template('''SELECT a.s_id,
							   e.s_name,
							   a.num,
							   a.num * a.c_p / d.av,
							   a.num * c.p / d.av,
							   a.c_p,
							   c.p,
							   SIGN(a.num) * (c.p / a.c_p - 1),
							   d.av / (c.p * 100),
							   a.num * c.p AS mv,
							   a.p_id,
							   f.p_name
						  FROM holding_cost a,
							   (SELECT MAX(t_date) AS max_date, s_id, p_id
								  FROM holding_cost
								 WHERE t_date <= TO_DATE('${DATE}', 'yyyy-mm-dd')
								 GROUP BY p_id, s_id) b,
							   (SELECT s_id, p
								  FROM security_price
								 WHERE t_date = TO_DATE('${DATE}', 'yyyy-mm-dd')) c,
							   (SELECT av, p_id
								  FROM nav_sc
								 WHERE t_date = TO_DATE('${DATE}', 'yyyy-mm-dd')) d,
							   security_info e,
							   product_info f
						 WHERE a.t_date = b.max_date
						   And a.s_id = b.s_id
						   And a.p_id = b.p_id
						   And a.num <> 0
						   And a.s_id = c.s_id
						   And a.s_id = e.s_id
						   And a.p_id = d.p_id
						   And a.p_id = f.p_id
						   AND a.p_id not in ('SC011','SC012','SC018','SC019','SC020','SC021','SC022','SC023','SC032','SC101','SC102','SC103','SC104','SC106')
						 ORDER BY a.p_id, mv DESC''')
	
	sql_text = sql_tpl.substitute(DATE=ref_date.isoformat())
	
	cursor = DBConnFactory().get_db_connection('ONSHOREDB').cursor()
	cursor.execute(sql_text)
	return cursor.fetchall()